#include "command.h"
#include <map>       // 用于 std::map  
#include <iomanip>   // 用于 std::setw (用于打印)
#include <stdexcept> // 用于 std::invalid_argument, std::out_of_range
#include <limits>    // std::numeric_limits 所需
#include <algorithm>
#include <cstring>
std::string extract_conditions(const std::string& query);

// 函数定义
std::string extract_conditions(const std::string& query) {
    std::string lower_query = query;
    // 将查询字符串转换为小写，以便不区分大小写地查找关键字
    std::transform(lower_query.begin(), lower_query.end(), lower_query.begin(), ::tolower);

    size_t where_pos = lower_query.find(" where ");
    size_t order_by_pos = lower_query.find(" order by ");

    if (where_pos != std::string::npos) {
        // 如果找到 "where"，则从 "where" 开始提取
        // 如果 "order by" 也存在且在 "where" 之后，它将被包含在内
        // +1 是为了去掉 " where " 前面的空格
        return query.substr(where_pos + 1);
    } else if (order_by_pos != std::string::npos) {
        // 如果没有 "where" 但有 "order by"，则从 "order by" 开始提取
        // +1 是为了去掉 " order by " 前面的空格
        return query.substr(order_by_pos + 1);
    }

    return ""; // 如果没有 "where" 或 "order by"，则返回空字符串
}
struct full_condition {
    string column_to_compare;//要比较的column
    string column_to_sort;//作为排序依据的column
    string op; //  "=", "<", ">", "<=", ">=", "!="
    ValueType value_to_compare;//要比较的值
    string original_value_str; // 存储原始字符串用于类型转换
    bool is_valid = false;//升序或者降序
};
vector<string> split_string(const string& s, char delimiter) {
    vector<string> tokens;
    string token;
    std::istringstream tokenStream(s);
    while (std::getline(tokenStream, token, delimiter)) {
        // 从 token 中修剪空白字符
        size_t start = token.find_first_not_of(" \t\n\r\f\v',' ");
        token = (start == string::npos) ? "" : token.substr(start);
        size_t end = token.find_last_not_of(" \t\n\r\f\v',' ");
        token = (end == string::npos) ? "" : token.substr(0, end + 1);
        // 如果 token 只包含空白字符（或为空，例如上一步的结果），则将其设为空字符串。否则，截取从开头到最后一个非空白字符（包含该字符）的子串，赋值回 token，从而移除后端空白
        if (!token.empty()) {
            tokens.push_back(token);
        }
    }
    return tokens;
}
string get_select_name(const string& args) {
    size_t from_pos = args.find(" from ");
    if (from_pos == string::npos) {
        error("Syntax error: 'FROM' keyword not found in SELECT statement.");
        return "";
    }

    size_t table_name_start = from_pos + strlen(" from "); // " from " 的长度是 6
    string remaining_args = args.substr(table_name_start);
    
    // 表名是 "from " 之后的第一个词
    std::istringstream iss(remaining_args);
    string table_name_token;
    iss >> table_name_token; // 提取第一个词

    if (table_name_token.empty()) {
        error("Syntax error: Table name not found after 'FROM' keyword.");
        return "";
    }
    return table_name_token;
}
bool compare(const ValueType& cell_value, const std::string& cond_str, const std::string& type, const std::string& op) {
    try {
        if (type == "int") {
            int cond_val = std::stoi(cond_str);
            if (std::holds_alternative<int>(cell_value)) {
                int cell_val = std::get<int>(cell_value);
                if (op == "=") return cell_val == cond_val;
                if (op == "<") return cell_val < cond_val;
                if (op == ">") return cell_val > cond_val;
                if (op == "<=") return cell_val <= cond_val;
                if (op == ">=") return cell_val >= cond_val;
                if (op == "!=") return cell_val != cond_val;
            }
        } else if (type == "float") {
            float cond_val = std::stof(cond_str);
            if (std::holds_alternative<float>(cell_value)) {
                float cell_val = std::get<float>(cell_value);
                if (op == "=") return cell_val == cond_val;
                if (op == "<") return cell_val < cond_val;
                if (op == ">") return cell_val > cond_val;
                if (op == "<=") return cell_val <= cond_val;
                if (op == ">=") return cell_val >= cond_val;
                if (op == "!=") return cell_val != cond_val;
            }
        } else if (type == "double") {
            double cond_val = std::stod(cond_str);
            if (std::holds_alternative<double>(cell_value)) {
                double cell_val = std::get<double>(cell_value);
                if (op == "=") return cell_val == cond_val;
                if (op == "<") return cell_val < cond_val;
                if (op == ">") return cell_val > cond_val;
                if (op == "<=") return cell_val <= cond_val;
                if (op == ">=") return cell_val >= cond_val;
                if (op == "!=") return cell_val != cond_val;
            }
        } else if (type == "string") {
            if (std::holds_alternative<std::string>(cell_value)) {
                const std::string& cell_val = std::get<std::string>(cell_value);
                if (op == "=") return cell_val == cond_str;
                if (op == "<") return cell_val < cond_str;
                if (op == ">") return cell_val > cond_str;
                if (op == "<=") return cell_val <= cond_str;
                if (op == ">=") return cell_val >= cond_str;
                if (op == "!=") return cell_val != cond_str;
            }
        }
    } catch (...) {
        // 转换失败，认为不匹配
    }
    return false;
}
void delete_table(string args, db *current_db)
{
    args =  "delete "+ args;
    string name = get_select_name(args);
    if (name.empty()) return;
    // 找目标表
    table* target_table = nullptr;
    for (auto& t : current_db->tables) {
        if (t.name == name) {
            target_table = &t;
            break;
        }
    }
    if (!target_table) {
        error("Table '%s' not found.", name.c_str());
        return;
    }
    size_t where_pos = args.find(" where ");
    if (where_pos == string::npos) {
        // 无where，全部删除
        for (auto& col : target_table->columns) {
            col.values.clear();
        }
        cout << "All records deleted from table '" << name << "'." << endl;
        return;
    }
    // 解析where条件
    string where_clause_str = args.substr(where_pos + strlen(" where "));
    where_clause_str.erase(0, where_clause_str.find_first_not_of(" \t\n\r\f\v"));
    where_clause_str.erase(where_clause_str.find_last_not_of(" \t\n\r\f\v") + 1);
    vector<string> ops = {"<=", ">=", "!=", "=", "<", ">"};
    size_t op_pos = string::npos;
    string op;
    int op_len = 0;
    for (const string& o : ops) {
        size_t curr = where_clause_str.find(o);
        if (curr != string::npos) {
            op_pos = curr;
            op = o;
            op_len = o.length();
            break;
        }
    }
    if (op_pos == string::npos) {
        error("Invalid WHERE clause: Operator not found.");
        return;
    }
    string colname = where_clause_str.substr(0, op_pos);
    string valstr = where_clause_str.substr(op_pos + op_len);
    colname.erase(0, colname.find_first_not_of(" \t\n\r\f\v"));
    colname.erase(colname.find_last_not_of(" \t\n\r\f\v") + 1);
    valstr.erase(0, valstr.find_first_not_of(" \t\n\r\f\v'\""));
    valstr.erase(valstr.find_last_not_of(" \t\n\r\f\v'\"") + 1);
    // 找列
    int col_idx = -1;
    string col_type;
    for (size_t i = 0; i < target_table->columns.size(); ++i) {
        if (target_table->columns[i].name == colname) {
            col_idx = i;
            col_type = target_table->columns[i].type;
            break;
        }
    }
    if (col_idx == -1) {
        error("Column '%s' not found in table '%s'.", colname.c_str(), name.c_str());
        return;
    }
    
    // 构造要删除的行索引
    vector<size_t> rows_to_delete;
    size_t nrows = target_table->columns[col_idx].values.size();
    for (size_t i = 0; i < nrows; ++i) {
        const ValueType& cell = target_table->columns[col_idx].values[i];
        bool match = compare(cell, valstr, col_type, op);
        if (match)
            rows_to_delete.push_back(i);
    }

    // 倒序删除所有列的对应行
    for (auto it = rows_to_delete.rbegin(); it != rows_to_delete.rend(); ++it) {
        size_t idx = *it;
        for (auto& col : target_table->columns) {
            if (col.values.size() > idx)
                col.values.erase(col.values.begin() + idx);
        }
    }
    cout << "Deleted " << rows_to_delete.size() << " record(s) in table '" << name << "'." << endl;
}
void select_table(string args, db *current_db)
{
    args = "select "+args;
    string lower_args = args;
    std::transform(lower_args.begin(), lower_args.end(), lower_args.begin(), ::tolower);

    size_t select_pos = lower_args.find("select ");
    size_t from_pos = lower_args.find(" from ");
    size_t where_pos = lower_args.find(" where ");
    size_t order_by_pos = lower_args.find(" order by ");

    if (from_pos == string::npos) {
        error("Syntax error: 'FROM' keyword not found in SELECT statement.");
        return;
    }

    string table_name_str = args.substr(from_pos + strlen(" from "));
    std::istringstream iss_table(table_name_str);
    string table_name;
    iss_table >> table_name;

    if (table_name.empty()) {
        error("Syntax error: Table name not found after 'FROM' keyword.");
        return;
    }

    string columns_str;
    if (select_pos != string::npos) {
        columns_str = args.substr(select_pos + strlen("select "), from_pos - (select_pos + strlen("select ")));
    } else {
        error("Syntax error: 'SELECT' keyword not found.");
        return;
    }
    // 去除列名前后的空格
    columns_str.erase(0, columns_str.find_first_not_of(" \t\n\r\f\v"));
    columns_str.erase(columns_str.find_last_not_of(" \t\n\r\f\v") + 1);


    string where_clause_str;
    string order_by_clause_str;

    if (where_pos != string::npos) {
        size_t end_where = (order_by_pos != string::npos && order_by_pos > where_pos) ? order_by_pos : args.length();
        where_clause_str = args.substr(where_pos + strlen(" where "), end_where - (where_pos + strlen(" where ")));
        // 去除where子句前后的空格
        where_clause_str.erase(0, where_clause_str.find_first_not_of(" \t\n\r\f\v"));
        where_clause_str.erase(where_clause_str.find_last_not_of(" \t\n\r\f\v") + 1);
    }

    if (order_by_pos != string::npos) {
        // 提取“order by”之后的完整字符串部分
        order_by_clause_str = args.substr(order_by_pos + strlen(" order by "));
        
        // // 如果存在尾随分号，则将其移除，因为它不属于用于解析的子句的一部分
        //并且如果不进行处理，可能会干扰后续对“ asc”或“ desc”的 rfind 操作
        if (!order_by_clause_str.empty() && order_by_clause_str.back() == ';') {
            order_by_clause_str.pop_back();
        }

        // 从提取的子句中修剪前后空白
        // 确保以后对column和asc/desc的解析工作正确。
        if (!order_by_clause_str.empty()) {
            size_t start = order_by_clause_str.find_first_not_of(" \t\n\r\f\v");
            if (start == std::string::npos) {
                order_by_clause_str.clear(); // All whitespace
            } else {
                size_t end = order_by_clause_str.find_last_not_of(" \t\n\r\f\v");
                order_by_clause_str = order_by_clause_str.substr(start, end - start + 1);
            }
        }
    }
    
    vector<string> selected_columns = split_string(columns_str, ',');

    full_condition condition;
    condition.is_valid = false; // 默认排序为升序或无排序

    // 解析 WHERE 条件 (简化版，仅支持单个条件，无AND/OR)
    if (!where_clause_str.empty()) {
        vector<string> ops = {"<=", ">=", "!=", "=", "<", ">"}; // 双字符运算符优先
        size_t op_pos_in_where = string::npos;
        int op_len = 0;

        for (const string& o : ops) {
            size_t current_op_pos = where_clause_str.find(o);
            if (current_op_pos != string::npos) {
                op_pos_in_where = current_op_pos;
                condition.op = o;
                op_len = o.length();
                break;
            }
        }

        if (op_pos_in_where != string::npos) {
            condition.column_to_compare = where_clause_str.substr(0, op_pos_in_where);
            // 去除比较列名前后的空格
            condition.column_to_compare.erase(0, condition.column_to_compare.find_first_not_of(" \t\n\r\f\v"));
            condition.column_to_compare.erase(condition.column_to_compare.find_last_not_of(" \t\n\r\f\v") + 1);

            condition.original_value_str = where_clause_str.substr(op_pos_in_where + op_len);
            // 去除比较值前后的空格和可能的引号
            condition.original_value_str.erase(0, condition.original_value_str.find_first_not_of(" \t\n\r\f\v'\""));
            condition.original_value_str.erase(condition.original_value_str.find_last_not_of(" \t\n\r\f\v'\"") + 1);
            // 注意：ValueType 的转换需要根据表定义进行，这里暂时只保存字符串
        } else {
            error("Syntax error: Invalid WHERE clause format. Operator not found or not supported.");
            // return; // 可以选择返回或继续执行（无条件）
        }
    }

    // 解析 ORDER BY 条件
    if (!order_by_clause_str.empty()) {
        std::string lower_order_by_clause = order_by_clause_str;
        std::transform(lower_order_by_clause.begin(), lower_order_by_clause.end(), lower_order_by_clause.begin(), ::tolower);
        
        size_t sort_order_pos = lower_order_by_clause.rfind(" asc");
        if (sort_order_pos != string::npos && sort_order_pos + strlen(" asc") == lower_order_by_clause.length()) {
            condition.column_to_sort = order_by_clause_str.substr(0, sort_order_pos);
            condition.is_valid = true; // true for asc
        } 
        else {
            sort_order_pos = lower_order_by_clause.rfind(" desc");
            if (sort_order_pos != string::npos && sort_order_pos + strlen(" desc") == lower_order_by_clause.length()) {
                condition.column_to_sort = order_by_clause_str.substr(0, sort_order_pos);
                condition.is_valid = false; // false for desc
            } else {
                // 默认升序
                condition.column_to_sort = order_by_clause_str;
                condition.is_valid = true;
            }
        }
        // 去除排序列名前后的空格
        condition.column_to_sort.erase(0, condition.column_to_sort.find_first_not_of(" \t\n\r\f\v"));
        condition.column_to_sort.erase(condition.column_to_sort.find_last_not_of(" \t\n\r\f\v") + 1);
    }


    // 查找表并执行操作
    table* target_table = nullptr; // 使用小写的 table 类型
    for (auto &table_ : current_db->tables) {
        if (table_.name == table_name) {
            target_table = &table_;
            break;
        }
    }

    if (!target_table) {
        // 构建错误消息字符串
        std::string error_msg = "Table '" + table_name + "' not found.";
        // 将 std::string 转换为 C 风格字符串并传递给 error 宏
        error("%s", error_msg.c_str());
        return;
    }

    // 接下来是筛选、排序和打印的逻辑


    // 临时打印解析结果，用于调试
    // cout << "Table Name: " << table_name << endl;
    // cout << "Selected Columns: ";
    // for (const auto& col : selected_columns) {
    //     cout << "'" << col << "' ";
    // }
    // cout << endl;

    // if (!where_clause_str.empty()) {
    //     cout << "Where Clause: '" << where_clause_str << "'" << endl;
    //     cout << "  Column to Compare: '" << condition.column_to_compare << "'" << endl;
    //     cout << "  Operator: '" << condition.op << "'" << endl;
    //     cout << "  Value to Compare: '" << condition.original_value_str << "'" << endl;
    // } else {
    //     cout << "No Where Clause." << endl;
    // }

    // if (!order_by_clause_str.empty()) {
    //     cout << "Order By Clause: '" << order_by_clause_str << "'" << endl;
    //     cout << "  Column to Sort: '" << condition.column_to_sort << "'" << endl;
    //     cout << "  Sort Order: " << (condition.is_valid ? "ASC" : "DESC") << endl;
    // } else {
    //     cout << "No Order By Clause." << endl;
    // }
    
    // 筛选记录
    vector<vector<ValueType>> filtered_rows;
    vector<int> original_row_indices; // 如果需要原始索引进行排序或更新

   int compare_column_idx = -1;
    std::string compare_column_type;
    if (!condition.column_to_compare.empty() && target_table) {
         for (size_t i = 0; i < target_table->columns.size(); ++i) {
            if (target_table->columns[i].name == condition.column_to_compare) {
                compare_column_idx = i;
                compare_column_type = target_table->columns[i].type;
                break;
            }
        }
    if (compare_column_idx == -1) {
        error("Column '%s' not found in table '%s' for WHERE clause.", condition.column_to_compare.c_str(), table_name.c_str());
        return;
    }
}
    // 遍历表的行
    if (target_table && !target_table->columns.empty()) {
        size_t num_rows = target_table->columns[0].values.size();
        for (size_t i = 0; i < num_rows; ++i) {
            bool row_matches = true;
            if (compare_column_idx != -1) {
                const ValueType& cell_value = target_table->columns[compare_column_idx].values[i];
                row_matches = compare(
                    cell_value,
                    condition.original_value_str,
                    compare_column_type,
                    condition.op
                );
            }
            if (row_matches) {
                std::vector<ValueType> current_row_values;
                for (const auto& col_meta : target_table->columns) {
                    if (i < col_meta.values.size()) {
                        current_row_values.push_back(col_meta.values[i]);
                    }
                }
                filtered_rows.push_back(current_row_values);
                original_row_indices.push_back(i);
            }
        }
    }

    // 排序记录
    if (!condition.column_to_sort.empty() && target_table && !filtered_rows.empty()) {
        int sort_column_idx = -1;
        for (size_t i = 0; i < target_table->columns.size(); ++i) {
            if (target_table->columns[i].name == condition.column_to_sort) {
                sort_column_idx = i;
                break;
            }
        }

        if (sort_column_idx == -1) {
            error("Column '%s' not found in table '%s' for ORDER BY clause.", condition.column_to_sort.c_str(), table_name.c_str());
            // 不return，允许继续打印未排序的结果，或者根据需求决定是否返回
        } else {
            std::sort(filtered_rows.begin(), filtered_rows.end(),
                [&](const vector<ValueType>& row_a, const vector<ValueType>& row_b) {
                // 确保排序列索引在行数据范围内
                if (static_cast<size_t>(sort_column_idx) >= row_a.size() || static_cast<size_t>(sort_column_idx) >= row_b.size()) {
                    // 如果索引越界，可以认为它们相等或按某种方式处理错误
                    // 例如，打印错误并返回 false
                    // std::cerr << "Sort column index out of bounds." << std::endl;
                    return false;
                }
                const ValueType& val_a = row_a[sort_column_idx];
                const ValueType& val_b = row_b[sort_column_idx];

                bool less_than = false;
                try {
                    if (std::holds_alternative<int>(val_a) && std::holds_alternative<int>(val_b)) {
                        less_than = std::get<int>(val_a) < std::get<int>(val_b);
                    } else if (std::holds_alternative<float>(val_a) && std::holds_alternative<float>(val_b)) {
                        less_than = std::get<float>(val_a) < std::get<float>(val_b);
                    } else if (std::holds_alternative<double>(val_a) && std::holds_alternative<double>(val_b)) {
                        less_than = std::get<double>(val_a) < std::get<double>(val_b);
                    } else if (std::holds_alternative<string>(val_a) && std::holds_alternative<string>(val_b)) {
                        less_than = std::get<string>(val_a) < std::get<string>(val_b);
                    } else {
                        // 类型不匹配，尝试转换为字符串比较
                        less_than = to_string(val_a) < to_string(val_b);
                    }
                } catch (const std::bad_variant_access& e) {
                    // std::cerr << "Bad variant access during sort: " << e.what() << std::endl;
                    // 在转换失败时，可以认为它们相等或按某种方式处理
                    return false; // 保持原始顺序或定义一个回退比较
                }
                return condition.is_valid ? less_than : !less_than; // is_valid true for ASC
            });
            // cout << "Sorted by column: " << condition.column_to_sort << (condition.is_valid ? " ASC" : " DESC") << endl; // 调试输出可以保留或移除
        }
    }

    // 打印结果
    if (target_table) {
        vector<int> column_indices_to_print;
        vector<string> column_names_to_print;

        if (selected_columns.size() == 1 && selected_columns[0] == "*") {
            for (size_t i = 0; i < target_table->columns.size(); ++i) {
                column_indices_to_print.push_back(i);
                column_names_to_print.push_back(target_table->columns[i].name);
            }
        } else {
            for (const string& sel_col_name : selected_columns) {
                bool found = false;
                for (size_t i = 0; i < target_table->columns.size(); ++i) {
                    if (target_table->columns[i].name == sel_col_name) {
                        column_indices_to_print.push_back(i);
                        column_names_to_print.push_back(sel_col_name);
                        found = true;
                        break;
                    }
                }
                if (!found) {
                    error("Column '%s' not found in table '%s' for SELECT list.", sel_col_name.c_str(), table_name.c_str());
                    // 可以选择不打印任何内容或只打印找到的列
                    // return; // 如果列不存在则不打印
                }
            }
        }

        if (column_indices_to_print.empty() && !(selected_columns.size() == 1 && selected_columns[0] == "*")) {
             // 如果指定了列但没有一列是有效的 (例如，所有指定的列名都不存在)
            cout << "No valid columns selected to display." << endl;
        } else if (filtered_rows.empty()) {
            cout << "No records found matching the criteria." << endl;
        }
         else {
            // 打印表头
            for (const auto& col_name : column_names_to_print) {
                cout << std::setw(15) << std::left << col_name; // 使用 setw 控制宽度
            }
            cout << endl;
            for (size_t i = 0; i < column_names_to_print.size(); ++i) {
                 cout << std::setw(15) << std::left << string(14, '-'); // 分隔线
            }
            cout << endl;

            // 打印数据行
            for (const auto& row : filtered_rows) {
                for (int col_idx : column_indices_to_print) {
                    if (static_cast<size_t>(col_idx) < row.size()) {
                        cout << std::setw(15) << std::left << to_string(row[col_idx]);
                    } else {
                        cout << std::setw(15) << std::left << "N/A"; // 数据缺失
                    }
                }
                cout << endl;
            }
            cout << "\nTotal records: " << filtered_rows.size() << endl;
        }
    } else {
        // target_table 为空指针的情况，虽然前面已经检查过
        error("Cannot print results: target table is null.");
    }
}
